Supplementary Problem 1533 Supplementary Problem 1533 The US

Supplementary Problem 15.33

Supplementary Problem 15.33 The U.S. Department of Commerce publishes a series of census documents referred to as Current Industrial Reports. Included in these documents are the manufacturers’ shipments, inventories, and orders over a 5-year period. Displayed here is a portion of these data representing the shipments of chemicals and allied products from January of year 1 through December of year 5. Time Period Chemicals and Allied Products ($ billion) Time Period Chemicals and Allied Products ($ billion) January (year 1) 23.701 January (year 2) 23.347 February 24.189 February 24.122 March 24.200 March 25.282 April 24.971 April 25.426 May 24.560 May 25.185 June 24.992 June 26.486 July 22.566 July 24.088 August 24.037 August 24.672 September 25.047 September 26.072 October 24.115 October 24.328 November 23.034 November 23.826 December 22.590 December 24.373 January (year 3) 24.207 January (year 4) 25.316 February 25.772 February 26.435 March 27.591 March 29.346 April 26.958 April 28.983 May 25.920 May 28.424 June 28.460 June 30.149 July 24.821 July 26.746 August 25.560 August 28.966 September 27.218 September 30.783 October 25.650 October 28.594 November 25.589 November 28.762 December 25.370 December 29.018 Time Period Chemicals and Allied Products ($ billion) January (year 5) 28.931 February 30.456 March 32.372 April 30.905 May 30.743 June 32.794 July 29.342 August 30.765 September 31.637 October 30.206 November 30.842 December 31.090 Use the seasonal indexes computed below to deseasonalize the data. Month Index Jan 95.35 Feb 99.69 Mar 106.75 Apr 103.99 May 100.99 June 106.96 July 94.53 Aug 99.60 Sept 104.16 Oct 97.04 Nov 95.75 Dec 95.19 What is the deseasonalized data value for June (year 3)? (Round your answer to 2 decimal places.)

Solution

I can solve this problem by using MS Excel,

Copy the data in the excel first,

You can use a pivot table to calculate the seasonal indices. To do that, follow these steps:

1. Move the date field to the Row Labels. Add historical sales to the Values area. You will have a pivot table identical to the original data set.

4. Select one of the date cells in the pivot table. Select Group Field from the Options tab of the ribbon.

It looks like

1537.84

5. In the Grouping dialog, choose only Months. I always say that you should never choose Months without also choosing Years; however, calculating seasonal indices is the one time that you can choose only Month,

In the next column, use the formula for finding deseasonalized data, =B2/index(B column,month(forecast(corresponding first row) and drag it you will get deseasonalized data.

Hope this will help you.